<?php
 include_once('../src/classes/class.mssql.php');
 session_start();
 if(isset ($_GET['searchOperations']))
 {
  $db = new MSSQL(); 
  
  $token  = $_GET['token'];
  $comtec_id_mainteiner = $_GET['id'];
  $comtec_id_user = $_SESSION['comtec_id_user'];
  $type = $_GET['type'];
    
  if ($_GET['year1']!="Año" && $_GET['month1'] != 0 && $_GET['day1']!=0){
   $date_start = $_GET['year1'].$_GET['month1'].$_GET['day1']." 00:00:00";
  }
  
  if ($_GET['year2']!="Año" && $_GET['month2'] != 0 && $_GET['day2']!=0){
   $date_end = $_GET['year2'].$_GET['month2'].$_GET['day2']." 00:00:00"; 
  }
  
  $operation_type = $_GET['operation_type'];
  $comtec_id_responsible = isset ($_GET['comtec_id_responsible'])? $_GET['comtec_id_responsible']:"";
  
  $owner = isset ($_GET['owner'])? $_GET['owner']:"";
  $machine = $_GET['machine'];
  
  if ($type == 1)
  {
  
   $tsql = "SELECT m.comtec_id_machine,m.model,op.comtec_id_operation,op.operation_type,m.brand,(r.name+' '+r.lastname1+' '+lastname2) as encargado,
             ow.company_name,CONVERT(varchar(12),op.operation_date_end,103) as fecha,m.cod_mainteiner,s.name_s,o.comtec_id_order,o.cod_order
            FROM ((((operation as op 
             INNER JOIN machines as m ON op.comtec_id_machine = m.comtec_id_machine) 
             INNER JOIN responsible as r ON op.comtec_id_responsible=r.comtec_id_responsible) 
             INNER JOIN owner as ow ON m.comtec_id_owner = ow.comtec_id_owner) 
             INNER JOIN orders as o ON o.comtec_id_order = op.comtec_id_order)
             INNER JOIN status_op as s ON op.comtec_id_status = s.id_s
            WHERE op.status_reg=1 AND m.comtec_id_mainteiner=$comtec_id_mainteiner";
  
  if($_SESSION["users"] == "clients"){
    $tsql .= " AND ow.comtec_id_user = $comtec_id_user ";
  }
  
  if($operation_type!=0)
  {
   $tsql .=  " AND op.operation_type=$operation_type";
  }
  
  if($owner != "")
  {
   $tsql .=  " AND (lower(ow.company_name) LIKE lower('$owner%') OR lower(ow.cod_owner) LIKE lower('$owner%')) ";  
  }
  
  if($machine!= "")
  {
   $tsql .=  " AND (lower(m.cod_mainteiner) LIKE lower('$machine%') OR lower(m.brand) LIKE lower('$machine%') OR lower(m.model) LIKE lower('$machine%') ) ";  
  }
  
  if($comtec_id_responsible!=0)
  {
   $tsql .=  " AND op.comtec_id_responsible=$comtec_id_responsible";
  }
  
  if(isset ($date_start))
  {
   if(isset ($date_end))
   {
     $tsql .= " AND (op.operation_date_end >= '$date_start' AND  op.operation_date_end <= '$date_end' )";
   }else
       {
        $tsql .= " AND op.operation_date_end >= '$date_start' ";
       }
  }else
      {
       if(isset ($date_end))
       {
        $tsql .= " AND  op.operation_date_end <= '$date_end' ";
       }
      }
  
  
  $tsql .=   " ORDER BY op.operation_date_end DESC";
  }else
      {
       $tsql = "SELECT m.comtec_id_machine,o.cod_order,o.comtec_id_order,o.operation_type,m.cod_mainteiner,m.brand,m.model,
                       (r.name+' '+r.lastname1+' '+r.lastname2) AS encargado,ow.company_name,
	               CONVERT (varchar,o.order_start_date,103) as fecha,
                       CONVERT (varchar,(SELECT TOP 1 op.operation_date_end
                                         FROM operation as op
                                         WHERE op.comtec_id_machine = m.comtec_id_machine AND op.status_reg=1
                                         ORDER BY op.operation_date_end DESC),103) AS last_operation,
	               (SELECT COUNT(*)
                        FROM [dbo].[orders] as o1 INNER JOIN [dbo].[operation] as op1 ON o1.comtec_id_order = op1.comtec_id_order
		        WHERE o1.comtec_id_order = o.comtec_id_order AND o1.status_reg=1 AND op1.status_reg=1 ) as cant,
                       (SELECT isnull(op1.comtec_id_operation,0)
                        FROM [dbo].[orders] as o1 INNER JOIN [dbo].[operation] as op1 ON o1.comtec_id_order = op1.comtec_id_order
		        WHERE o1.comtec_id_order = o.comtec_id_order AND o1.status_reg=1 AND op1.status_reg=1 ) as comtec_id_operation
                FROM (([dbo].[orders] as o                
                 INNER JOIN [dbo].[machines] as m ON o.comtec_id_machine = m.comtec_id_machine)
                 INNER JOIN [dbo].[owner] as ow ON m.comtec_id_owner = ow.comtec_id_owner)
                 INNER JOIN [dbo].[responsible] as r ON r.comtec_id_responsible = o.comtec_id_responsible                 
                WHERE o.comtec_id_mainteiner = $comtec_id_mainteiner AND o.status_reg=1 ";
        
       if($_SESSION["users"] == "clients"){
        $tsql .= " AND ow.comtec_id_user = $comtec_id_user ";
       }
  
       if($operation_type!=0)
       {
        $tsql .=  " AND o.operation_type=$operation_type";
       }
  
       if($owner != "")
       {
        $tsql .=  " AND (lower(ow.company_name) LIKE lower('$owner%') OR lower(ow.cod_owner) LIKE lower('$owner%')) ";  
       }
  
       if($machine!= "")
       {
        $tsql .=  " AND (lower(m.cod_mainteiner) LIKE lower('$machine%') OR lower(m.brand) LIKE lower('$machine%') OR lower(m.model) LIKE lower('$machine%') ) ";  
       }
  
       if($comtec_id_responsible!=0)
       {
        $tsql .=  " AND o.comtec_id_responsible=$comtec_id_responsible";
       }
  
       if(isset ($date_start))
       {
        if(isset ($date_end))
        {
         $tsql .= " AND (o.order_end_date >= '$date_start' AND  o.order_end_date <= '$date_end' )";
        }else
            {
             $tsql .= " AND o.order_end_date >= '$date_start' ";
            }
       }else
           {
            if(isset ($date_end))
            {
             $tsql .= " AND  o.order_end_date <= '$date_end' ";
            }
           }  
   $tsql .= " ORDER BY o.order_end_date DESC";
  }
  
  $stmt = $db->query($tsql); 
  
 }

?>
<br />
<h3>Resultado de busqueda:</h3>
<br />
<?php
   $texto = "<table class='table'>
             <tr>";
    
   if ($type == 1)
   {
    
    //operaciones
       
    if($_SESSION["users"] == "managers"){
     $texto .= "<th colspan=2></th>";
    }else{ if($_SESSION["users"] != "clients") { $texto .= "<th></th>";}}            
    
    $texto .= "<th>C&oacute;digo</th>
              <th>Tipo</th>
              <th>C&oacute;d. m&aacute;quina</th>
              <th>Marca</th>
              <th>Modelo</th>
              <th>Propietario</th>
              <th>Encargado</th>
              <th>Fecha oper.</th>
              <th>C&oacute;d. orden</th>";
    if(strcmp($_SESSION["users"],"clients")!=0){
     $texto .= "<th>Estado</th>";
    }
    $texto .= "</tr>";  
   $cont = 0;
  while ($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)){
   $cont += 1;     
   $comtec_id_operation=$row['comtec_id_operation'];
   $comtec_id_machine=$row['comtec_id_machine'];
   $operation_type=$row['operation_type'];
   $brand=$row['brand'];
   $encargado=$row['encargado'];
   $company_name=$row['company_name'];
   $fecha=$row['fecha'];
   $cod_mainteiner=$row['cod_mainteiner'];
   $model=$row['model'];
   $status=$row['name_s'];
   $comtec_id_order=$row['comtec_id_order'];
   $cod_order=$row['cod_order'];
   $texto .= '<tr>';
   if($_SESSION["users"]!="clients"){
    $url_edit='http://'.$_SERVER['HTTP_HOST'].'/iconos/edit.png';
    $texto .= "<td><a title='Editar operacion' href='javascript:loadOperationSearch($comtec_id_operation,$comtec_id_machine);'>";
    $texto .= '<img src="'.$url_edit.'" alt="editar" style="border: 0px;" /></a></td>';     
   }
   if($_SESSION["users"]=="managers"){
    $url_delete = 'http://'.$_SERVER['HTTP_HOST'].'/iconos/delete.png';
    $texto .= '<td><a title="Eliminar operacion" href="javascript:deleteOperationSearch('.$comtec_id_operation.','.$comtec_id_machine.');"><img src="'.$url_delete.'" alt="eliminar" style="border: 0px;" /></a></td>';   
   }
    $texto .= '<td><a title="Ver operacion" href="javascript:operationSearch('.$comtec_id_operation.','.$comtec_id_machine.');">'.$comtec_id_operation.'</a></td>';
    switch ($operation_type) {
             case 1: 
                 $tipo = "Mantenci&oacute;n";
             break;
             case 2: 
                 $tipo = "Emergencia";
             break; 
             case 3: 
                 $tipo = "Instalaci&oacute;n";
             break;
             case 4:
                 $tipo = "Reparaci&oacute;n";
             break;    
             default: $tipo = '';
             break;    
            }
    $texto .= '<td>'.$tipo.'</td>';
    $texto .= '<td>'.$cod_mainteiner.'</td>';
    $texto .= '<td>'.$model.'</td>';
    $texto .= '<td>'.$brand.'</td>';
    $texto .= '<td>'.$company_name.'</td>';
    $texto .= '<td>'.$encargado.'</td>';
    $texto .= '<td>'.$fecha.'</td>';
    $texto .= '<td><a href="javascript:orderSearch('.$comtec_id_order.');">'.$cod_order.'</a></td>';
    if($_SESSION['users']!="clients"){     
     $texto .= '<td>'.$status.'</td>';
    }
   $texto .= '</tr>';
  }
}else{
    
      //ordenes de trabajo
     
      $texto .= "<th colspan=3></th>
                 <th>C&oacute;digo</th>
                 <th>Tipo</th>
                 <th>C&oacute;d. m&aacute;quina</th>
                 <th>Marca</th>
                 <th>Modelo</th>            
                 <th>Propietario</th>
                 <th>Encargado</th>
                 <th>Fecha últ. oper.</th>
                 <th>Fecha a operar</th>
                </tr>";  
    $cont = 0;
    while ($row = sqlsrv_fetch_array($stmt,SQLSRV_FETCH_ASSOC)){ 
       $cant=$row['cant'];
       $cont += 1;
       $cod_order=$row['cod_order'];
       $comtec_id_order=$row['comtec_id_order'];
       $operation_type=$row['operation_type'];
       $cod_mainteiner=$row['cod_mainteiner'];
       $brand=$row['brand'];
       $model=$row['model'];
       $encargado=$row['encargado'];
       $company_name=$row['company_name'];
       $fecha=$row['fecha'];
       $last_operation=$row['last_operation'];
       $comtec_id_machine=$row['comtec_id_machine'];
       $comtec_id_operation=$row['comtec_id_operation'];
       $texto  .= "<tr>";
       $url_edit = 'http://'.$_SERVER['HTTP_HOST'].'/iconos/edit.png';
       $texto  .= "<td><a href='javascript:loadOrderSearch(".$comtec_id_order.");'><img src='$url_edit' alt='Editar' title='Editar orden de trabajo' style='border: 0px;' /></a></td>";
       $url_delete = 'http://'.$_SERVER['HTTP_HOST'].'/iconos/delete.png';
       $texto  .= "<td><a href='javascript:deleteOrderSearch(".$comtec_id_order.");'><img src='$url_delete' alt='Eliminar' title='Eliminar orden de trabajo' style='border: 0px;' /></a></td>";            
       if($cant > 0){
        $url_view='http://'.$_SERVER['HTTP_HOST'].'/iconos/viewOperation.png';
        $texto .= "<td><a href='javascript:operationSearch(".$comtec_id_operation.",".$comtec_id_machine.");'><img src='$url_view' alt='ver' title='Ver operacion' style='border: 0px;' /></a></td>";
       }else{
        $url_add='http://'.$_SERVER['HTTP_HOST'].'/iconos/addOperation.png';
        $texto .= "<td><a href='javascript:loadAddOperationSearch(".$comtec_id_order.");'><img src='$url_add' alt='agregar' title='Agregar operacion' style='border: 0px;' /></a></td>";
       }      
       $cod_order   = ($cod_order != "")? $cod_order : "-";
       $texto .= "<td><a href='javascript:orderSearch(".$comtec_id_order.");' title='Ver orden de trabajo'>$cod_order</a></td>";
       $Tipo = "-";
       switch ($operation_type) {
        case 1: 
             $Tipo = "Mantenci&oacute;n";
        break;
        case 2: 
             $Tipo = "Emergencia";
        break; 
        case 3: 
             $Tipo = "Instalaci&oacute;n";
        break;
        case 4:
             $Tipo = "Reparaci&oacute;n";
        break;    
       }
       $texto  .= "<td>$Tipo</td>";     
       $cod_mainteiner   = ($cod_mainteiner != "")? $cod_mainteiner : "-";
       $texto  .= "<td>$cod_mainteiner</td>";
       $brand   = ($brand != "")? $brand : "-";
       $texto  .= "<td>$brand</td>";
       $model   = ($model != "")? $model : "-";
       $texto  .= "<td>$model</td>";
       $texto  .= "<td>$company_name</td>";
       $texto  .= "<td>$encargado</td>";
       $last_operation   = ($last_operation != "")? $last_operation : "-";
       $texto  .= "<td>$last_operation</td>";
       $texto  .= "<td>$fecha</td>"; 
       $texto .= "</tr>";
      }     
}
$texto .='</table>'; 
$texto .='<div id="result"></div>';
echo "<p class='total'>Total: $cont</p>".$texto;
?>  